/*
This code produces the distribution of county-level MtM LTVs in the NMDB,
for use in Python code to produce figure A1.
*/

********************************************************************************
** Save smaller versions of the data for each quarter

use "$NMDB/nmdb_20210113_v130_loans.dta", clear

forvalues qq = 61(1)92 {
preserve
keep if !mi(balq`qq') // keep loans with a positive balance as of the quarter (q78 for June 2017)
keep value hpi_orig hpiq`qq' sample balq`qq' open2ndq`qq' close2ndq`qq'
save "$DATA_OUT/processing/nmdb_loans_q`qq'", replace
restore
}


********************************************************************************
** Get the weight in each LTV bucket, relative to the weight in the LTV == 80 bucket, 
** by quarter and save results in Stata files

forvalues qq = 61(1)92 {
use "$DATA_OUT/processing/nmdb_loans_q`qq'", clear

keep value hpi_orig hpiq`qq' sample balq`qq' open2ndq`qq' close2ndq`qq'
drop if balq==0

replace open2ndq`qq' = 0 if mi(open2ndq`qq')
replace close2ndq`qq' = 0 if mi(close2ndq`qq')

gen MtM_value = value * (hpiq`qq' / hpi_orig)
gen total_balance = balq`qq' + open2ndq`qq'  + close2ndq`qq'

gen MtM_cLTV = 100 * (total_balance / MtM_value)
keep sample MtM_cLTV

gen LTV_bucket = round(MtM_cLTV)

* winsorize LTV at 300
replace LTV_bucket = 300 if LTV_bucket>300 & !mi(LTV_bucket)
collapse (sum) sample_wgt, by(LTV_bucket)

gen wgt_at_80 = sample_wgt if LTV_bucket == 80
egen max_wgt_at_80 = max(wgt_at_80)

gen relative_weight = sample_wgt / max_wgt_at_80

keep relative_weight LTV_bucket

save "$DATA_OUT/processing/ltv_distribution_q`qq'", replace

}

********************************************************************************
** Finally, create a csv file with the relative LTV distribution by quarter
use "$DATA_OUT/processing/ltv_distribution_q61", clear
rename relative_weight relative_weight_q61

forvalues qq = 62(1)92 {
	merge 1:1 LTV_bucket using "$DATA_OUT/processing/ltv_distribution_q`qq'", nogen
	rename relative_weight relative_weight_q`qq'
	replace relative_weight_q`qq' = 0 if mi(relative_weight_q`qq')
}

export delimited using "$OUTPUT/MtM_cLTV_distribution_q61_to_q92.csv", replace
